const express = require("express");
const router = express.Router();
const eae = require("express-async-errors");

const moment = require("moment");

const rds = require("./rds");
const mui = require("./mui");
const tutils = require("./tutils");
const logger = require("./logger");
const dbq = require("./dbq");
const task = require("./task");

function checkPermission(target = "user", permission = "crud") {
    return async (req, res, next) => {
        const authUser = req.session.authUserInfo;
        const totemUID = authUser.totemUID;
        const crmROLE = authUser.crmROLE;

        let title = "图腾生命医学应用提示";
        let tips = `mta-crm u#${totemUID} ip: ${req.ip} url: ${req.originalUrl}`;
        let message = `您没有此操作所需的${permission.toUpperCase()}权限`;
        let description =
            "请联系管理员或<a href='https://support.qq.com/products/615894'>提交问题工单</a>";

        if (crmROLE === "")
            return res.render("warn", { title, message, description, tips });
        if (crmROLE === "crm-admin" || crmROLE === "crm-service") return next(); // 用户拥有特殊角色，直接通过

        const targetMap = { user: "uid", order: "oid", task: "tid" };
        const target_id = req.query[targetMap[target]];

        let granted = false;
        if (target == "user")
            try{
            granted = await dbq.checkUserPermission(
                target_id,
                authUser,
                permission
            );
            }catch(error){
                console.log('ERROR:', `mta-crm u#${totemUID} ip: ${req.ip} url: ${req.originalUrl}`); //DEBUG
            }
        if (target == "order")
            granted = await dbq.checkOrderPermission(
                target_id,
                authUser,
                permission
            );
        if (target == "task")
            granted = await dbq.checkTaskPermission(
                target_id,
                authUser,
                permission
            );

        // 用户具有所需权限，可以继续执行下一个中间件或路由处理程序
        if (granted) return next();

        return res.render("warn", { title, message, description, tips });
    };
}

async function fetchClientReferralData(
    connection,
    clientId,
    startDate,
    endDate
) {
    const clientQuery = `
        SELECT spreader_uid as clientId, recruited_count, recruited_list, mta_recruited_count, mta_recruited_list,
            buyer_count, buyer_list, first_order_count, first_order_list, npo_user_list, qpo_count, qpo_list, qpo_user_list
        FROM eb_spreader_daily_report
        WHERE spreader_uid = '${clientId}' and FROM_UNIXTIME(noon_time) >= '${startDate}' AND FROM_UNIXTIME(noon_time) <= '${endDate}'
    `;
    const [clientRows] = await connection.execute(clientQuery);
    let totalRecruitedCount = 0,
        totalBuyerCount = 0,
        totalFirstOrderCount = 0,
        totalQpoCount = 0,
        totalMtaRecruitedCount = 0;
    let totalRecruitedList = [],
        totalMtaRecruitedList = [],
        totalBuyerList = [],
        totalFirstOrderList = [],
        totalQpoList = [],
        totalNpoUserList = [],
        totalQpoUserList = [];
    if (clientRows.length > 0) {
        for (let clientRow of clientRows) {
            totalRecruitedCount += clientRow.recruited_count;
            totalRecruitedList = totalRecruitedList.concat(
                JSON.parse(clientRow.recruited_list || "[]")
            );
            totalMtaRecruitedCount += clientRow.mta_recruited_count;
            totalMtaRecruitedList = totalMtaRecruitedList.concat(
                JSON.parse(clientRow.mta_recruited_list || "[]")
            );
            totalBuyerCount += clientRow.buyer_count;
            totalBuyerList = totalBuyerList.concat(
                JSON.parse(clientRow.buyer_list || "[]")
            );
            totalFirstOrderCount += clientRow.first_order_count;
            totalFirstOrderList = totalFirstOrderList.concat(
                JSON.parse(clientRow.first_order_list || "[]")
            );
            totalNpoUserList = totalNpoUserList.concat(
                JSON.parse(clientRow.npo_user_list || "[]")
            );
            totalQpoCount += clientRow.qpo_count;
            totalQpoList = totalQpoList.concat(
                JSON.parse(clientRow.qpo_list || "[]")
            );
            totalQpoUserList = totalQpoUserList.concat(
                JSON.parse(clientRow.qpo_user_list || "[]")
            );
        }
    }
    const result = {
        period: `[${startDate}, ${endDate}]`,
        clientId: clientId,
        recruitedCount: totalRecruitedCount,
        recruitedList: totalRecruitedList,
        mtaRecruitedList: totalMtaRecruitedList,
        buyerCount: totalBuyerCount,
        buyerList: totalBuyerList,
        firstOrderCount: totalFirstOrderCount,
        firstOrderList: totalFirstOrderList,
        npoUserList: totalNpoUserList,
        qpoCount: totalQpoCount,
        qpoUserList: totalQpoUserList,
        qpoList: totalQpoList,
    };
    return result;
}

async function fetchPromotionData(connection, repCode, startDate, endDate) {
    const initialQuery = `
            SELECT COALESCE(SUM(recruited_count),0) as totalRecruited,
                GROUP_CONCAT(DISTINCT CASE WHEN recruited_count > 0 THEN spreader_uid END) AS activeClients,
                COALESCE(SUM(first_order_count),0) as totalFirstOrders,
                GROUP_CONCAT(DISTINCT CASE WHEN first_order_count > 0 THEN spreader_uid END) AS firstOrderClients
            FROM eb_spreader_daily_report
            WHERE rep_code = '${repCode}' and FROM_UNIXTIME(noon_time) >= '${startDate}' AND FROM_UNIXTIME(noon_time) <= '${endDate}'
            GROUP BY rep_id
        `;
    const [rows] = await connection.execute(initialQuery);
    if (rows.length === 0) {
        return {
            title: `${repCode} 推广详情`,
            excerpt: `活跃客户: 0, 开单客户: 0，总计推荐：0, 总计首单: 0`,
            excerpt: `活跃客户: 0, 开单客户: 0`,
            description: `[${startDate}, ${endDate}] [Rep: ${repCode}]`,
            activeClientIds: [],
            activeClientDataMap: new Map(),
            firstOrderClientIds: [],
            firstOrderClientDataMap: new Map(),
        };
    }
    const row = rows[0];
    const activeClientIds = row.activeClients
        ? row.activeClients.split(",")
        : [];
    const firstOrderClientIds = row.firstOrderClients
        ? row.firstOrderClients.split(",")
        : [];

    const activeClientDataMap = new Map();
    const firstOrderClientDataMap = new Map();

    for (const clientId of activeClientIds) {
        const clientData = await fetchClientReferralData(
            connection,
            clientId,
            startDate,
            endDate
        );
        activeClientDataMap.set(clientId, clientData);
    }
    for (const clientId of firstOrderClientIds) {
        const clientData = await fetchClientReferralData(
            connection,
            clientId,
            startDate,
            endDate
        );
        firstOrderClientDataMap.set(clientId, clientData);
    }

    return {
        title: `${repCode} 推广详情`,
        excerpt: `活跃客户: ${activeClientDataMap.size}, 开单客户: ${firstOrderClientDataMap.size}，推荐用户：${row.totalRecruited}, 成交首单: ${row.totalFirstOrders}`,
        description: `[${startDate}, ${endDate}] [Rep: ${repCode}]`,
        activeClientIds,
        activeClientDataMap,
        firstOrderClientIds,
        firstOrderClientDataMap,
    };
}

router.get("/get-promotion-data", async (req, res) => {
    const { rep, view, onset } = req.query;
    const connection = await rds.pool.getConnection();

    // Calculate the end date based on the view
    const onsetFormat = onset.length == 8 ? "YY-MM-DD" : "YYYY-MM-DD";
    const beginDate = moment(onset, onsetFormat).format("YYYY-MM-DD");
    const endDate = moment(onset, onsetFormat)
        .add(1, view)
        .subtract(1, "day")
        .format("YYYY-MM-DD");

    // Fetch the referral data from the database
    const referralData = await fetchPromotionData(
        connection,
        rep,
        beginDate,
        endDate
    );
    referralData.activeClientDataMap = Object.fromEntries(
        referralData.activeClientDataMap
    );
    referralData.firstOrderClientDataMap = Object.fromEntries(
        referralData.firstOrderClientDataMap
    );

    // Release the database connection
    connection.release();

    res.json(referralData);
});

async function fetchReferralData(connection, cid, onset, endDate) {
    const query0 = `
        SELECT COALESCE(sum(recruited_count), 0) as sumR, sum(first_order_count) as sumF, sum(qpo_count) as sumQ, COALESCE(sum(buyer_count), 0) as sumB
        FROM eb_spreader_daily_report
        WHERE spreader_uid = ? AND FROM_UNIXTIME(noon_time) >= ? AND FROM_UNIXTIME(noon_time) <= ?
    `;
    const [rows0] = await connection.execute(query0, [cid, onset, endDate]);
    const sumR = rows0.length > 0 ? rows0[0].sumR || 0 : 0;
    const sumF = rows0.length > 0 ? rows0[0].sumF || 0 : 0;
    const sumQ = rows0.length > 0 ? rows0[0].sumQ || 0 : 0;
    const sumB = rows0.length > 0 ? rows0[0].sumB || 0 : 0;

    const query1 = `
        SELECT recruited_list #, sum(recruited_count) as sumR, sum(first_order_count) as sumF, sum(qpo_count) as sumQ, sum(buyer_count) as sumB
        FROM eb_spreader_daily_report
        WHERE spreader_uid = ? AND FROM_UNIXTIME(noon_time) >= ? AND FROM_UNIXTIME(noon_time) <= ?
    `;

    const [rows1] = await connection.execute(query1, [cid, onset, endDate]);
    const recruitedList = rows1
        .map((row) => JSON.parse(row.recruited_list || "[]"))
        .flat();

    if (recruitedList.length === 0) {
        return {
            title: `CID#${cid}荐新数据详情`,
            excerpt: `新推荐用户零，其中首单零，合格零`,
            description: `[${onset}, ${endDate}] 新荐${sumR} 首单${sumF} 合格${sumQ} 购买${sumB}`,
            recruitedUserList: [],
        };
    }
    const query2 = `
        SELECT eb_user.uid, eb_user.real_name AS uname, eb_user.login_type as mta, eb_user.last_purchased AS last_purchased,
            eb_user.add_time AS add_time, eb_store_order_npo.pay_time AS npo_time, eb_store_order_qpo.pay_time AS qpo_time
        FROM eb_user
        LEFT JOIN eb_store_order eb_store_order_npo ON eb_user.uid = eb_store_order_npo.uid AND eb_store_order_npo.paid = 1 AND eb_store_order_npo.tags LIKE '%NPO%'
        LEFT JOIN eb_store_order eb_store_order_qpo ON eb_user.uid = eb_store_order_qpo.uid AND eb_store_order_qpo.paid = 1 AND eb_store_order_qpo.tags LIKE '%QPO%'
        WHERE eb_user.uid IN (${recruitedList.map((uid) => "?").join(",")})
        ORDER BY eb_user.add_time DESC
    `;

    const [rows2] = await connection.execute(query2, recruitedList);

    const recruitedUserList = rows2.map((row) => ({
        uid: row.uid,
        uname: row.uname,
        mta: row.mta,
        last_purchased: row.last_purchased,
        add_time: moment.unix(row.add_time).format("YY-MM-DD"),
        npo_time: row.npo_time
            ? moment.unix(row.npo_time).format("YY-MM-DD")
            : null,
        qpo_time: row.qpo_time
            ? moment.unix(row.qpo_time).format("YY-MM-DD")
            : null,
    }));

    return {
        title: `CID#${cid}荐新数据详情`,
        excerpt: `新推荐用户${recruitedUserList.length}，其中首单${countOrdersByType(recruitedUserList, "npo_time")}，合格${countOrdersByType(recruitedUserList, "qpo_time")}`,
        description: `[${onset}, ${endDate}] 新荐${sumR} 首单${sumF} 合格${sumQ} 购买${sumB}`,
        recruitedUserList,
    };
}

function countOrdersByType(recruitedUserList, type) {
    return recruitedUserList.reduce((count, user) => {
        return user[type] ? count + 1 : count;
    }, 0);
}

router.get("/referrals", async (req, res) => {
    let {
        cid = "",
        rep = "",
        team = "",
        view = "MONTH",
        page = 1,
        limit = 50,
        begin = "2020-06-14",
        end = moment().format("YYYY-MM-DD"),
    } = req.query;

    // 统计周期，支持WEEK MONTH YEAR,不能支持DAY，因为天数按月计算会重复，DAYOFDAY也没有
    view = view.toUpperCase();
    // 根据用户角色自动限制其搜索范围
    if (
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
    ) {
        rep = req.session.authUserInfo.repCODE;
        team = req.session.authUserInfo.repTEAM;
    }
    //if ( req.session.authUserInfo.uid == 20450 || req.session.authUserInfo.uid == 20901 ) rep = 'PRB';

    const cidCondition = cid == "" ? "" : `AND spreader_uid = ${cid}`;

    const weekSql = `
            SELECT
                CONCAT(DATE_FORMAT(FROM_UNIXTIME(noon_time),'%y'), '#', LPAD(WEEK(FROM_UNIXTIME(noon_time)), 2, '0')) AS period,
                DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(noon_time), INTERVAL(1-DAYOFWEEK(FROM_UNIXTIME(noon_time))) DAY), '%Y-%m-%d') AS onset,
                spreader_uid as cid, promoter_name as cname,
                GROUP_CONCAT(DISTINCT rep_code) AS rep, #GROUP_CONCAT(DISTINCT rep_team) AS team,
                COALESCE(SUM(recruited_count),0) AS R, #GROUP_CONCAT(recruited_list) AS rList,
                COALESCE(SUM(mta_recruited_count),0) AS mta,
                COALESCE(SUM(first_order_count),0) AS F, #$GROUP_CONCAT(first_order_list) AS fList,
                COALESCE(SUM(qpo_count),0) AS Q, #GROUP_CONCAT(qpo_list) AS qList,
                COALESCE(SUM(buyer_count),0) AS B, #GROUP_CONCAT(buyer_list) AS bList
                COALESCE(SUM(actual_weixin_paid),0) AS S
            FROM
                eb_spreader_daily_report
            WHERE
                FROM_UNIXTIME(noon_time) >= ? AND FROM_UNIXTIME(noon_time) <= ?
                and rep_code like ? AND rep_team like ?
                ${cidCondition}
            GROUP BY
                period, spreader_uid
            ORDER BY
                onset DESC, R DESC, F DESC, Q DESC
            `;

    const viewSql = weekSql.replace(/WEEK/g, view);
    const connection = await rds.pool.getConnection();

    const [totalRows] = await connection.execute(
        `SELECT COUNT(*) as total, sum(R) as sumR, sum(F) as sumF, sum(Q) as sumQ FROM (${viewSql}) AS subquery`,
        [begin, end, `%${rep}%`, `%${team}%`]
    );
    let { total, sumR, sumF, sumQ } = totalRows[0] ?? {
        total: 0,
        sumR: 0,
        sumF: 0,
        sumQ: 0,
    };
    if (total == 0) {
        sumR = sumF = sumQ = 0;
    } //当没有记录时total = 0, 其它为null
    const totalPages = Math.ceil(total / limit);

    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;

    const [rows] = await connection.execute(
        viewSql + ` LIMIT ${startIndex}, ${endIndex}`,
        [begin, end, `%${rep}%`, `%${team}%`]
    );
    const modifiedRows = rows.map((row) => {
        const { period, onset, cid, cname, rep, R, mta } = row;
        const id = cid; //这里是强行构造
        const aname = cname.charAt(0) + "** [" + rep + "]";
        const rmta = `${R}${mta > 0 ? "/" + mta : ""}`;
        const modifiedRow = { period, onset, id, aname, rmta, ...row };
        delete modifiedRow.R;
        delete modifiedRow.mta;
        delete modifiedRow.cname;
        delete modifiedRow.cid;
        delete modifiedRow.team;
        delete modifiedRow.rep;
        return modifiedRow;
    });
    const fields = [
        "序号",
        "起始日期",
        "CID",
        "客户 [REP]",
        "新荐",
        "首单",
        "合格",
        "买家",
        "实销",
    ];

    const params = req.query;
    delete params.page; //删除req.query中的page参数以便后面增加
    const link = `/apps/crm/referrals?${new URLSearchParams(params).toString()}`;
    const paginator = mui.generatePaginator(3, totalPages, page, link);

    const viewType =
        view === "WEEK"
            ? "按周统计"
            : view === "MONTH"
              ? "按月统计"
              : view === "YEAR"
                ? "按年统计"
                : "其他周期";
    const title = "会员荐新数据";
    const excerpt = `新荐${sumR} 首单${sumF} 合格${sumQ}`;
    const description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()} 筛选条件：[${viewType}] [C#${cid ? cid : ""}] [代表${rep}] [部门${team}]`;

    res.render("crm/referrals", {
        title: title,
        excerpt: excerpt,
        description: description,
        fields: fields,
        rows: modifiedRows,
        paginator: paginator,
        view,
        cid,
        rep,
        team,
        limit,
        begin,
        end,
    });

    connection.release(); //如果使用pool就应该relase
});

router.get("/get-referral-data", async (req, res) => {
    const { cid, view, onset } = req.query;
    const connection = await rds.pool.getConnection();

    // Calculate the end date based on the view
    const onsetFormat = onset.length == 8 ? "YY-MM-DD" : "YYYY-MM-DD";
    const beginDate = moment(onset, onsetFormat).format("YYYY-MM-DD");
    const endDate = moment(onset, onsetFormat)
        .add(1, view)
        .subtract(1, "day")
        .format("YYYY-MM-DD");

    // Fetch the referral data from the database
    const referralData = await fetchReferralData(
        connection,
        cid,
        beginDate,
        endDate
    );

    // Release the database connection
    connection.release();

    res.json(referralData);
});

// 定义promotions路由
router.get("/promotions", async (req, res) => {
    let {
        rep = "",
        team = "",
        view = "MONTH",
        page = 1,
        limit = 50,
        begin = moment().startOf("year").format("YYYY-MM-DD"),
        end = moment().format("YYYY-MM-DD"),
    } = req.query;

    // 统计周期，支持WEEK MONTH YEAR,不能支持DAY，因为天数按月计算会重复，DAYOFDAY也没有
    view = view.toUpperCase();
    // 根据用户角色自动限制其搜索范围
    if (
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
    ) {
        rep = req.session.authUserInfo.repCODE;
        team = req.session.authUserInfo.repTEAM;
    }

    const periodFormat =
        "CONCAT(DATE_FORMAT(FROM_UNIXTIME(noon_time), '%y'), '#', LPAD(WEEK(FROM_UNIXTIME(noon_time)), 2, '0'))".replace(
            /WEEK/g,
            view
        );
    const onsetFormat =
        "DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(noon_time), INTERVAL (1 - DAYOFWEEK(FROM_UNIXTIME(noon_time))) DAY), '%Y-%m-%d')".replace(
            /WEEK/g,
            view
        );

    // 修改 where 条件，将 end 日期延伸到当天的最后一秒
    const where = ` FROM_UNIXTIME(noon_time) >= '${begin} 00:00:00' AND FROM_UNIXTIME(noon_time) <= '${end} 23:59:59' AND rep_code LIKE '%${rep}%' AND rep_team LIKE '%${team == "ALL" ? "" : team}%'`;
    const query = `
        SELECT ${periodFormat} AS period, ${onsetFormat} AS onset, rep_code AS rep,
            COUNT(DISTINCT CASE WHEN recruited_count > 0 THEN spreader_uid END) AS A,
            COUNT(DISTINCT CASE WHEN first_order_count > 0 THEN spreader_uid END) AS K,
            SUM(recruited_count) AS R,
            SUM(first_order_count) AS F,
            SUM(qpo_count) AS Q,
            SUM(actual_weixin_paid) AS S,
            GROUP_CONCAT(DISTINCT CASE WHEN recruited_count > 0 THEN spreader_uid END) AS AList,
            GROUP_CONCAT(DISTINCT CASE WHEN first_order_count > 0 THEN spreader_uid END) AS KList,
            GROUP_CONCAT(CASE WHEN first_order_list != '[]' THEN JSON_UNQUOTE(JSON_EXTRACT(first_order_list, '$[*]')) END) AS concat_first_order_string
        FROM eb_spreader_daily_report
        WHERE ${where}
        GROUP BY period, rep_code
        ORDER BY period DESC, A DESC, K DESC, R DESC
    `;

    const querySumA = ` SELECT COUNT(DISTINCT CASE WHEN recruited_count > 0 THEN spreader_uid END) AS sumA FROM eb_spreader_daily_report WHERE ${where} `;
    const querySumK = ` SELECT COUNT(DISTINCT CASE WHEN first_order_count > 0 THEN spreader_uid END) AS sumK FROM eb_spreader_daily_report WHERE ${where} `;
    const querySumTotal = ` SELECT COUNT(*) as total, sum(R) as sumR, sum(F) as sumF, sum(Q) as sumQ, sum(S) as sumS, (${querySumA}) as sumA, (${querySumK}) as sumK FROM (${query}) AS subquery`;

    const connection = await rds.pool.getConnection();
    const [totalRows] = await connection.execute(querySumTotal);

    let { total, sumR, sumF, sumQ, sumS, sumA, sumK } = totalRows[0] ?? {
        total: 0,
        sumR: 0,
        sumF: 0,
        sumQ: 0,
        sumS: 0,
        sumA: 0,
        sumK: 0,
    };
    if (total == 0) {
        sumR = sumF = sumQ = sumS = sumA = sumK = 0;
    } //当没有记录时total = 0, 其它为null
    const totalPages = Math.ceil(total / limit);
    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;

    const [rows] = await connection.execute(
        query + ` LIMIT ${startIndex}, ${endIndex}`
    );

    function convertString(inputString) {
        // 移除字符串两端的引号和中括号
        inputString = inputString.replace(/[\[\]']/g, "");
        // 用逗号分割字符串并去除空格
        const parts = inputString.split(",").map((part) => part.trim());
        // 重新拼接成新的字符串
        return "[" + parts.join(",") + "]";
    }

    // 步骤 1：解析 first_order_list 字段获取首单订单号列表
    function parseFirstOrderList(firstOrderList) {
        try {
            // 尝试解析 JSON 格式的 first_order_list
            const orderIds = JSON.parse(firstOrderList);
            return Array.isArray(orderIds) ? orderIds : [];
        } catch (error) {
            // 解析失败，返回空列表
            return [];
        }
    }

    // 步骤 2：构建动态 SQL 查询
    const calculateFirstOrderPaidPrice = (orderIds) => {
        return `
            (SELECT IFNULL(SUM(pay_price)-SUM(refund_price), 0) as actual_weixin_paid FROM eb_store_order WHERE id IN (${orderIds.join(",")}) and paid = 1 and pay_type = 'weixin')
        `;
    };

    // 步骤 3：将 first_order_paid_price 添加到查询结果 rows 中
    for (let row of rows) {
        // 示例：将 first_order_paid_price 添加到 row 中
        row.actual_weixin_paid = ""; //非管理用户不显示首单金额统计

        const concat_string = row.concat_first_order_string;
        if (concat_string) {
            const merged_string = convertString(concat_string);
            const firstOrderList = parseFirstOrderList(merged_string);
            if (
                (req.session.crmROLE === "crm-admin" ||
                    req.session.crmROLE === "crm-gmoffc") &&
                firstOrderList.length > 0
            ) {
                const firstOrderPaidPriceQuery =
                    calculateFirstOrderPaidPrice(firstOrderList);
                const [results] = await connection.execute(
                    firstOrderPaidPriceQuery
                );
                row.actual_weixin_paid = results[0]?.actual_weixin_paid;
            }
            row.concat_first_order_string = merged_string; //改变实际显示的首单列表
        }

        // 你可以根据需要将 firstOrderPaidPriceQuery 添加到查询中
        // 例如，你可以将其添加到 query 中的 SELECT 子句
    }

    // 现在 rows 中的每个记录都包含了 first_order_paid_price 字段，表示首单订单的支付总额

    connection.release();

    const fields = [
        "序号",
        "起始日期",
        "代表",
        "活跃",
        "开单",
        "新荐",
        "首单",
        "合格",
        "实销",
        "活跃客户",
        "开单客户",
        "fList",
        "fAmount",
    ];
    const params = req.query;
    delete params.page; //删除req.query中的page参数以便后面增加
    const link = `/apps/crm/promotions?${new URLSearchParams(params).toString()}`;
    const paginator = mui.generatePaginator(3, totalPages, page, link);

    const viewType =
        view === "WEEK"
            ? "按周统计"
            : view === "MONTH"
              ? "按月统计"
              : view === "YEAR"
                ? "按年统计"
                : "其他周期";
    const title = "代表推广数据";
    const excerpt = `活跃${sumA} 开单${sumK} 新荐${sumR} 首单${sumF} 合格${sumQ} 销量${parseInt(sumS)}`;
    const description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()} 筛选条件：[${viewType}] [代表${rep}] [部门${team}]`;

    res.render("crm/promotions", {
        title: title,
        excerpt: excerpt,
        description: description,
        fields: fields,
        rows: rows,
        paginator: paginator,
        begin,
        end,
        view,
        rep,
        team,
    });
});

//这是销售线索的显示 LIST TEMPLATE
router.get("/sales-leads", async (req, res) => {
    let {
        userId = "",
        creator = "",
        follower = "",
        stage = "",
        contact = "",
        source = "",
        remark = "",
        forecast = "",
        page = 1,
        limit = 50,
        begin = "2020-12-12",
        end = moment().format("YYYY-MM-DD"),
    } = req.query;

    if (
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel" ||
        req.session.crmROLE === "crm-mkting"
    )
        userId = req.session.authUserInfo.uid;
    const userCondition =
        userId == ""
            ? ""
            : ` and ( l.creator_uid = ${userId} or l.follower_uid = ${userId} )`;
    const creatorCondition =
        creator == "" ? "" : ` and l.creator_uid = ${creator}`;
    const followerCondition =
        follower == "" ? "" : ` and l.follower_uid = ${follower}`;
    const stageCondition = stage == "" ? "" : ` and l.stage_uid = ${stage}`;
    const contactCondition =
        contact == ""
            ? ""
            : ` and ( l.contact like '%${contact}%' or l.organization like '%${contact}%' or l.title like '%${contact}%' or l.phone like '%${contact}%' or l.wechat like '%${contact}%' or l.email like '%${contact}%' )`;
    const sourceCondition =
        source == "" ? "" : ` and l.source like '${source}'`;
    const remarkCondition =
        source == "" ? "" : ` and l.source like '${source}'`;
    const forecastCondition =
        forecast == "" ? "" : ` and l.forecast ${forecast}`; // >0 ; >5 etc.
    const timeCondition = ` and l.update_time BETWEEN STR_TO_DATE('${begin} 00:00:00', '%Y-%m-%d  %H:%i:%s') AND STR_TO_DATE('${end} 23:59:59', '%Y-%m-%d  %H:%i:%s')`;

    const query = `
        SELECT l.*
        FROM tutem_mall.sales_leads l
        WHERE l.id > 0 ${userCondition} ${creatorCondition} ${followerCondition} ${stageCondition} ${contactCondition} ${sourceCondition} ${remarkCondition} ${forecastCondition} ${timeCondition}
        ORDER BY l.update_time DESC
    `;

    const [totalRows] = await rds.query(
        `SELECT COUNT(*) as total, sum(stage*sales_forecast) as sumF FROM (${query}) AS subquery`
    );
    let { total, sumF } = totalRows[0] ?? { total: 0, sumF: 0 };
    if (total == 0) {
        sumF = 0;
    } //当没有记录时total = 0, 其它为null
    const totalPages = Math.ceil(total / limit);
    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;
    const params = req.query;
    delete params.page; //删除req.query中的page参数以便后面增加
    const link = `/apps/crm/sales-leads?${new URLSearchParams(params).toString()}`;

    const title = `线索列表`;
    const excerpt = `线索总数${total} 预计总销${sumF}`;
    let description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()} 筛选条件：[U#${userId}] [C#${creator}] [F#${follower}]<br>`;
    description += stage == "" ? "[线索阶段] " : `[线索阶段: ${stage}] `;
    description += contact == "" ? "[联系信息] " : `[联系信息：${contact}] `;
    description += source == "" ? "[线索来源] " : `[线索来源：${source}] `;
    description += remark == "" ? "[备注信息] " : `[备注信息：${remark}] `;
    description += forecast == "" ? "[销量预计] " : `[销量预计：${forecast}] `;
    const paginator = mui.generatePaginator(3, totalPages, page, link);
    const rows = await dbq.querySalesLeads(
        query + ` LIMIT ${startIndex}, ${endIndex}`
    );

    res.render("crm/sales-leads", {
        totemUID: req.session.totemUID,
        title,
        excerpt,
        description,
        paginator,
        rows,
        begin,
        end,
        userId,
        creator,
        follower,
        stage,
        contact,
        source,
        remark,
        forecast,
        limit,
    });
});

router.get("/orders", async (req, res) => {
    //uinfo 用户信息（姓名或手机） pinfo 商品信息 minfo 备注或档案信息 tinfo 订单标签信息
    let {
        rep = "",
        oid = "",
        uid = "",
        cid = "",
        uinfo = "",
        pinfo = "",
        minfo = "",
        tinfo = "",
        page = 1,
        limit = 50,
        begin = moment().startOf("month").format("YYYY-MM-DD"),
        end = moment().format("YYYY-MM-DD"),
    } = req.query;

    if (
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
    )
        rep = req.session.authUserInfo.repCODE;
    //if ( req.session.authUserInfo.uid == 20450 || req.session.authUserInfo.uid == 20901 ) rep = 'PRB';

    const repCondition = ` and ( o.tags like '%-${rep}%' or u.rep_code like '%${rep}%' )`; // 这样解决用户的直接rep是使用者但订单标记不是该rep的问题
    const oidCondition = oid == "" ? "" : ` and o.id = ${oid}`;
    const uidCondition = uid == "" ? "" : ` and o.uid = ${uid}`;
    const cidCondition = cid == "" ? "" : ` and u.spread_uid = ${cid}`;
    const uinfoCondition =
        uinfo == ""
            ? ""
            : ` and ( o.real_name like '%${uinfo}%' or o.user_phone like '%${uinfo}%' or o.user_address like '%${uinfo}%' ) `;
    const minfoCondition =
        minfo == ""
            ? ""
            : ` and ( o.mark like '%${minfo}%' or o.remark like '%${minfo}%' )`;
    const tinfoCondition = tinfo == "" ? "" : ` and o.tags like '%${tinfo}%'`;
    const pinfoCondition = pinfo == "" ? "" : ` and p.text like '%${pinfo}%'`;
    const timeCondition = ` and DATE_FORMAT(FROM_UNIXTIME(o.pay_time), '%Y-%m-%d') >= '${begin}' AND DATE_FORMAT(FROM_UNIXTIME(o.pay_time), '%Y-%m-%d') <= '${end}'`;

    const query = `
        SELECT
            o.id, from_unixtime(o.pay_time) as paid_at, o.status, o.uid, u.real_name AS uname, concat(f.title, ': ', f.memo) as last_memo,
            u.spread_uid AS cid, u.cs_aid AS sid, u.rep_code as urep,
            o.tags,o.remark, o.user_address as address, o.user_phone as phone, o.real_name as customer,
            o.pay_type, o.pay_price, o.refund_price, (o.pay_price - o.refund_price) as actual_paid,
            CASE WHEN o.pay_type = 'weixin' THEN o.pay_price - o.refund_price ELSE 0.00 END AS weixin_actual_paid,
            CASE WHEN o.pay_type = 'yue' THEN o.pay_price - o.refund_price ELSE 0.00 END AS tupay_actual_paid,
            o.delivery_id
        FROM
            tutem_mall.eb_store_order o
            LEFT JOIN tutem_mall.eb_user u ON ( o.uid = u.uid )
            LEFT JOIN tutem_mall.eb_pickup_book p ON ( o.id = p.oid )
            LEFT JOIN (
                SELECT *, ROW_NUMBER() OVER(PARTITION BY uid ORDER BY id DESC) AS row_num FROM tutem_mall.eb_user_profile
            ) f on (o.uid = f.uid and f.row_num = 1)
        WHERE
            o.paid = 1 ${timeCondition} ${repCondition} ${oidCondition} ${uidCondition} ${cidCondition} ${uinfoCondition} ${minfoCondition} ${tinfoCondition} ${pinfoCondition}
        ORDER BY
            o.pay_time DESC
    `;

    const connection = await rds.pool.getConnection();
    const [totalRows] = await connection.execute(
        `SELECT COUNT(*) as total, sum(weixin_actual_paid) as sumP, sum(tupay_actual_paid) as sumT FROM (${query}) AS subquery`
    );
    let { total, sumP, sumT } = totalRows[0] ?? { total: 0, sumP: 0, sumT: 0 };
    if (total == 0) {
        sumP = sumT = 0;
    } //当没有记录时total = 0, 其它为null
    const totalPages = Math.ceil(total / limit);
    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;

    // 执行查询操作，获取结果rows
    const [rows] = await connection.execute(
        query + ` LIMIT ${startIndex}, ${endIndex}`
    );
    connection.release(); //如果使用pool就应该relase

    const modifiedRows = rows.map((row) => {
        let {
            id,
            paid_at,
            status,
            uid,
            uname,
            last_memo,
            cid,
            sid,
            tags,
            remark,
            address,
            phone,
            customer,
            pay_type,
            weixin_actual_paid,
            tupay_actual_paid,
            delviery_id,
        } = row;

        if (
            req.session.crmROLE !== "crm-admin" &&
            req.session.crmROLE !== "crm-gmoffc" &&
            req.session.crmROLE !== "crm-service"
        ) {
            uname = tutils.maskString(uname);
            address = tutils.maskString(address);
            phone = tutils.maskString(phone);
            customer = tutils.maskString(customer);
        }

        //const modifiedRow = {...row, uname, address, phone, customer};
        const modifiedRow = {
            id,
            paid_at,
            status,
            uid,
            uname,
            last_memo,
            cid,
            sid,
            tags,
            remark,
            address,
            phone,
            customer,
            pay_type,
            weixin_actual_paid,
            tupay_actual_paid,
            delviery_id,
        };

        return modifiedRow;
    });

    const fields = [
        "id",
        "paid_at",
        "status",
        "uid",
        "uname",
        "last_memo",
        "cid",
        "sid",
        "tags",
        "remark",
        "address",
        "phone",
        "customer",
        "pay_type",
        "weixin_actual_paid",
        "tupay_actual_paid",
        "delviery_id",
    ];
    const params = req.query;
    delete params.page; //删除req.query中的page参数以便后面增加
    const link = `/apps/crm/orders?${new URLSearchParams(params).toString()}`;
    const paginator = mui.generatePaginator(3, totalPages, page, link);

    const title = `订单列表`;
    const excerpt = `订单总数${total} 微信实付${sumP} 图贝实付${sumT}`;
    const description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()} 筛选条件： [代表${rep}] [U#${uid}] [C#${cid}] [用户${uinfo}]<br>[小票${pinfo}] [标签${tinfo}] [备注${minfo}]`;

    res.render("crm/orders", {
        title: title,
        excerpt: excerpt,
        description: description,
        fields: fields,
        rows: modifiedRows,
        paginator: paginator,
        begin,
        end,
        oid,
        uid,
        cid,
        uinfo,
        pinfo,
        minfo,
        tinfo,
    });
});

router.get("/disclose-user-info", async (req, res) => {
    const { uid = 0, page = 1, limit = 50 } = req.query;
    let user = await dbq.getUser(uid);

    const query = `SELECT * FROM eb_user_profile WHERE uid = ${uid} and is_hide = 0 order by id desc LIMIT ${(page - 1) * limit}, ${page * limit}`;
    let profiles = await dbq.queryUserProfiles(query, [], req.session.crmROLE);
    const title = "用户详情";

    const asker = req.session.authUserInfo;
    const granted =
        asker.crmROLE == "crm-admin" ||
        asker.crmROLE == "crm-gmoffc" ||
        asker.crmROLE == "crm-mkting" ||
        asker.crmROLE == "crm-servie" ||
        (asker.repID != 0 && asker.repID == user.rep_id); // || ( asker.repID != 0 && asker.repID == user.crep_id ) ;

    if (!granted) {
        let {
            real_name,
            nickname,
            agency,
            department,
            title,
            phone,
            now_money,
            integral,
            last_memo,
        } = user;
        real_name = tutils.maskString(real_name);
        nickname = tutils.maskString(nickname);
        agency = tutils.maskString(agency, "shuffle");
        department = tutils.maskString(department, "shuffle");
        title = tutils.maskString(title, "shuffle");
        phone = tutils.maskString(phone);
        now_money = "--";
        integral = "--";
        last_memo = tutils.maskString(last_memo, "shuffle");
        user = {
            ...user,
            real_name,
            nickname,
            agency,
            department,
            title,
            phone,
            now_money,
            integral,
            last_memo,
        };
    }

    res.render("crm/user", { title, user, profiles, uid });
    return;
});

router.get("/users", async (req, res) => {
    //uid urep（用户的代表）cid crep(渠道的代表）uinfo(name, phone, nickname), pinfo(profile), minfo(mark), ginfo(goods_list) aid（已分配的客服 aid）
    let {
        uid = "",
        urep = "",
        cid = "",
        crep = "",
        aid = "",
        uinfo = "",
        pinfo = "",
        minfo = "",
        ginfo = "",
        page = 1,
        limit = 50,
        begin = "2020-12-12",
        end = moment().format("YYYY-MM-DD"),
    } = req.query;

    const asker = req.session.authUserInfo;

    const uidCondition = uid == "" ? "" : ` and u.uid = ${uid}`;
    const urepCondition = urep == "" ? "" : ` and u.rep_code like '%${urep}%'`;
    const cidCondition = cid == "" ? "" : ` and u.spread_uid = ${cid}`;

    if (
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
    )
        crep = req.session.authUserInfo.repCODE;
    //if ( req.session.authUserInfo.uid == 20450 || req.session.authUserInfo.uid == 20901 ) crep = 'PRB';
    const crepCondition =
        crep == ""
            ? ""
            : ` and ( c.rep_code like '%${crep}%' or u.rep_code like '%${crep}')`; //完美解决两种身份的问题

    const aidCondition = aid == "" ? "" : ` and u.cs_aid = ${aid}`;
    uinfo = decodeURIComponent(uinfo);
    const uinfoCondition =
        uinfo == ""
            ? ""
            : ` and ( u.real_name like '%${uinfo}%' or u.phone like '%${uinfo}%' or u.nickname like '%${uinfo}%' ) `;

    pinfo = decodeURIComponent(pinfo);
    const keywords = pinfo.split(" "); // 将 pinfo 拆分为关键词数组
    const keywordConditions = keywords.map(
        (keyword) =>
            `u.uid in ( select uid from tutem_mall.eb_user_profile where memo LIKE '%${keyword}%')`
    ); // 创建一个数组来存储每个关键词的条件
    const pinfoCondition =
        pinfo == "" ? "" : ` and ${keywordConditions.join(" and ")}`; // 将多个关键词条件组合为一个包含多个 AND 连接的条件语句

    minfo = decodeURIComponent(minfo);
    const minfoCondition =
        minfo == "" ? "" : ` and ( u.mark like '%${minfo}%' ) `;

    ginfo = decodeURIComponent(ginfo);
    const ginfoCondition =
        ginfo == "" ? "" : ` and ( u.goods_list like '%${ginfo}%' ) `;

    const timeCondition = ` and DATE_FORMAT(FROM_UNIXTIME(u.add_time), '%Y-%m-%d') >= '${begin}' AND DATE_FORMAT(FROM_UNIXTIME(u.add_time), '%Y-%m-%d') <= '${end}'`;
    const query = `
        SELECT
            u.*,
            w.subscribe, w.headimgurl,
            f.memo as last_memo, f.confidentiality as memo_confidentiality, f.add_time as memo_time,
            u.spread_uid as cid, c.rep_code as crep, c.real_name as cname, c.promoter_type as ctype, c.is_promoter as cpromoter, c.agency as cagency, c.department as cdep
        FROM
            tutem_mall.eb_user u
            LEFT JOIN tutem_mall.eb_user c on (u.spread_uid = c.uid)
            LEFT JOIN tutem_mall.eb_wechat_user w on (w.uid = u.uid)
            LEFT JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY uid ORDER BY id DESC) AS row_num FROM tutem_mall.eb_user_profile) f on (u.uid = f.uid and f.row_num = 1)
        WHERE
            u.uid > 0 ${uidCondition} ${urepCondition} ${cidCondition} ${crepCondition} ${aidCondition}
            ${uinfoCondition} ${pinfoCondition} ${timeCondition} ${minfoCondition} ${ginfoCondition}
        ORDER BY
            u.uid DESC
        `;

    const [totalRows] = await rds.query(
        `SELECT COUNT(*) as total, sum(actual_paid) as sumC, sum(now_money) as sumP, sum(integral) as sumT FROM (${query}) AS subquery`
    );
    let { total, sumC, sumP, sumT } = totalRows[0] ?? {
        total: 0,
        sumC: 0,
        sumP: 0,
        sumT: 0,
    };
    if (total == 0) {
        sumP = sumT = 0;
    } //当没有记录时total = 0, 其它为null
    const totalPages = Math.ceil(total / limit);
    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;

    // 执行查询操作，获取结果rows
    const [rows] = await rds.query(query + ` LIMIT ${startIndex}, ${endIndex}`);

    const modifiedRows = rows.map((row) => {
        let {
            phone,
            agency,
            department,
            title,
            last_memo,
            memo_confidentiality,
            memo_time,
            goods_list,
            mark,
        } = row;
        phone = phone || "";
        agency = agency || "";
        last_memo = last_memo || "";
        memo_confidentiality = memo_confidentiality || "Unclassified"; //记录中字段本身为null值的必须转换
        let updated_at = moment.unix(row.add_time).format("YY-MM-DD HH:mm:ss");
        if (memo_time && memo_time > 0)
            updated_at = moment.unix(memo_time).format("YY-MM-DD HH:mm:ss");

        const granted =
            asker.crmROLE == "crm-admin" ||
            asker.crmROLE == "crm-gmoffc" ||
            asker.crmROLE == "crm-mkting" ||
            asker.crmROLE == "crm-servie" ||
            (asker.repID != 0 && asker.repID == row.rep_id); // || ( asker.repID != 0 && asker.repID == row.crep_id ) ;

        if (!granted) {
            phone = tutils.maskString(phone);
            if (memo_confidentiality.includes("Secret")) {
                last_memo = tutils.maskString(last_memo, "shuffle");
            }
        }
        const avatar =
            row.headimgurl ||
            row.avatar ||
            "https://totemlife.cn/apps/public/weapp-cover-300x300.png";

        let basic = `${row.status == 0 ? '<span title="高危用户">👿</span>' : ""} `;
        basic += `${row.is_promoter == 1 ? '<span title="推广会员"><i class="fa fa-rss"></i></span>' : ""} `;
        basic += `${row.real_name} <span style='color:#c1c1c1'>${row.nickname}</span> `;
        basic += `${row.subscribe == 0 ? '<span title="未关注公号"><i class="fa-regular fa-bell-slash"></i></span>' : ""} `;
        basic += `${row.login_type == "mta" ? '<span title="MTA首荐"><i class="fa-solid fa-shield-halved"></i></span>' : ""} <br>`;
        basic += `${phone} ${agency} ${department} ${title}<br>`;
        basic += `<span style='color:#c1c1c1'>`;
        basic += `<span title="${row.cid === 0 || !row.cname ? "无上级推荐人" : row.cname + " " || ""}${row.cagency || ""} ${row.cdep || ""} ${row.crep || ""}">C${row.cid} ${row.cpromoter === 1 ? '<i class="fa fa-rss"></i>' : ""}</span>`;
        basic += `A${row.cs_aid}R${row.rep_id}${row.rep_id > 0 ? "-" + row.rep_code : ""} P${row.is_promoter}T${row.promoter_type} L${row.level}Q${row.is_qp}`;
        basic += `</span>`;

        const overview = `${goods_list}<br>${mark}<br>${memo_confidentiality.includes("Secret") ? "🔒" : ""}${last_memo}`;
        const modifiedRow = {
            ...row,
            last_memo,
            avatar,
            phone,
            agency,
            department,
            title,
            basic,
            overview,
            updated_at,
        }; //const modifiedRow = {uid: row.uid, info: row.mark};

        return modifiedRow;
    });

    //const fields = [ 'uid', '基础信息', '用户概览' ];
    const params = req.query;
    delete params.page; //删除req.query中的page参数以便后面增加
    const link = `/apps/crm/users?${new URLSearchParams(params).toString()}`;
    const paginator = mui.generatePaginator(3, totalPages, page, link);

    const title = `用户列表`;
    let excerpt = `用户总数${total} `;
    if (req.session.crmROLE == "crm-admin")
        excerpt += `消费${parseInt(sumC)} 图贝${parseInt(sumP)} 金豆${parseInt(sumT)}`;
    let description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()} 筛选条件：[U#${uid}] [C#${cid}] [A#${aid}]<br>`;
    description += urep == "" ? "[UREP] " : `[UREP: ${urep}] `;
    description += crep == "" ? "[CREP] " : `[CREP: ${crep}] `;
    description += uinfo == "" ? "[信息] " : `[信息：${uinfo}] `;
    description += pinfo == "" ? "[档案] " : `[档案：${pinfo}] `;
    description += minfo == "" ? "[备注] " : `[备注：${minfo}] `;
    description += ginfo == "" ? "[购买] " : `[购买：${ginfo}] `;

    res.render("crm/users", {
        totemUID: req.session.totemUID,
        title: title,
        excerpt: excerpt,
        description: description,
        rows: modifiedRows,
        paginator: paginator,
        begin,
        end,
        uid,
        urep,
        cid,
        crep,
        aid,
        uinfo,
        pinfo,
        minfo,
        ginfo,
    });
});

router.get("/tasks", async (req, res) => {
    const today = moment().format("YYYY-MM-DD");
    let {
        askerUid = "",
        id = "",
        publisherUid = "",
        assigneeUid = "",
        code = "",
        status = "",
        tinfo = "",
        page = 1,
        limit = 50,
        begin = "2020-12-12",
        end = today,
    } = req.query;

    const asker = req.session.authUserInfo;

    if (
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
    )
        askerUid = asker.uid;
    const askerUidCondition =
        askerUid == ""
            ? ""
            : ` and ( t.assignee_uid = ${askerUid} or t.publisher_uid =  ${askerUid})`;

    const idCondition = id == "" ? "" : ` and t.id = ${uid}`;
    const publisherUidCondition =
        publisherUid == "" ? "" : ` and t.publisher_uid = ${publisherUid}`;
    const assigneeUidCondition =
        assigneeUid == "" ? "" : ` and t.assignee_uid = ${assigneeUid}`;

    const codeCondition = code == "" ? "" : ` and t.code like '%${code}%'`;
    const statusCondition =
        status == "" ? "" : ` and t.status like '%${status}%'`;

    tinfo = decodeURIComponent(tinfo);
    const tkeywords = tinfo.split(" "); // 将 tinfo 拆分为关键词数组
    const tkeywordConditions = tkeywords.map(
        (tkeyword) =>
            ` ( t.title like '%${tkeyword}%' or t.description like '%${tkeyword}%' )`
    ); // 创建一个数组来存储每个关键词的条件
    const tinfoCondition =
        tinfo == "" ? "" : ` and ${tkeywordConditions.join(" and ")}`; // 将多个关键词条件组合为一个包含多个 AND 连接的条件语句

    const timeCondition = ` and t.created_at BETWEEN STR_TO_DATE('${begin} 00:00:00', '%Y-%m-%d  %H:%i:%s') AND STR_TO_DATE('${end} 23:59:59', '%Y-%m-%d  %H:%i:%s')`;
    const query = `
        SELECT t.*, p.real_name as publisherName, a.real_name as assigneeName
        FROM tutem_mall.tasks t
            LEFT JOIN tutem_mall.eb_user p on (p.uid = t.publisher_uid)
            LEFT JOIN tutem_mall.eb_user a on (a.uid = t.assignee_uid)
        WHERE
            t.id > 0  ${askerUidCondition} ${idCondition} ${publisherUidCondition} ${assigneeUidCondition} ${codeCondition} ${statusCondition} ${tinfoCondition} ${timeCondition}
        ORDER BY
            t.id DESC
        `;

    const [totalRows] = await rds.query(
        `SELECT COUNT(*) as total FROM (${query}) AS subquery`
    );
    let { total } = totalRows[0] ?? { total: 0 };
    const totalPages = Math.ceil(total / limit);
    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;

    // 执行查询操作，获取结果rows
    const [rows] = await rds.query(query + ` LIMIT ${startIndex}, ${endIndex}`);

    const modifiedRows = rows.map((row) => {
        const publisherInfo = `${row.publisherName} <span style='color:#c1c1c1'>${row.publisher_uid}</span> `;
        const updated_at = moment(row.updated_at).format("YYYY-MM-DD HH:mm:ss");
        const created_at = moment(row.created_at).format("YYYY-MM-DD HH:mm:ss");
        const taskInfo = task.taskStatusInfos[row.status];
        const taskAction = task.taskStatusActions[row.status];
        const modifiedRow = {
            ...row,
            taskInfo,
            taskAction,
            created_at,
            updated_at,
            publisherInfo,
        };

        return modifiedRow;
    });

    //const fields = [ 'uid', '基础信息', '用户概览' ];
    const params = req.query;
    delete params.page; //删除req.query中的page参数以便后面增加
    const link = `/apps/crm/tasks?${new URLSearchParams(params).toString()}`;
    const paginator = mui.generatePaginator(3, totalPages, page, link);

    const title = `任务列表`;
    let excerpt = `任务总数${total} `;
    let description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()}-U#${asker.uid} 筛选条件：[U#${askerUid}] [P#${publisherUid}] [A#${assigneeUid}]<br>`;
    description += code == "" ? "[代码] " : `[代码: ${code}] `;
    description += status == "" ? "[状态] " : `[状态: ${status}] `;
    description += tinfo == "" ? "[任务信息] " : `[任务信息：${tinfo}] `;

    res.render("crm/tasks", {
        totemUID: req.session.totemUID,
        title: title,
        excerpt: excerpt,
        description: description,
        rows: modifiedRows,
        paginator: paginator,
        begin,
        end,
        askerUid,
        publisherUid,
        assigneeUid,
        code,
        status,
        tinfo,
    });
});

//创建销售线索的入口
router.get("/create-sales-lead", async (req, res) => {
    const uid = req.session.totemUID; // 获取当前用户的UID
    const query = `SELECT uid, real_name, roles FROM eb_user WHERE roles LIKE '%crm%' and not roles LIKE '%crm-advisor%' order by roles`;
    try {
        const [users] = await rds.query(query);
        const followerOptions = users.map((user) => ({
            value: user.uid,
            name: `${tutils.getCrmRole(user.roles)} ${user.real_name} U#${user.uid}`,
            selected: user.uid === uid ? true : false,
        }));
        const stageOptions = [
            { value: "0.1", name: "10%-新线索", selected: true },
        ];
        res.render("crm/create-sales-lead", {
            followers: followerOptions,
            stages: stageOptions,
        });
    } catch (error) {
        console.error("Error fetching users:", error);
        res.status(500).send("Server error");
    }
});

//这里是给使用者按照用户角度来管理其档案的入口
router.get("/create-user-profile", checkPermission(), async (req, res) => {
    const totemUID = req.session.totemUID;

    let {
        uid = 0,
        task_id = 0,
        pinfo = "",
        page = 1,
        limit = 50,
        begin = "2020-12-12",
        end = moment().format("YYYY-MM-DD"),
    } = req.query;
    const user = await dbq.getUser(uid);

    let task_name = "无关联任务";
    let notification = "";
    const task = await dbq.getTask(task_id);
    if (task) {
        notification =
            task?.status == "finished"
                ? "该任务已经完成，您可以录入新的跟进记录"
                : `${task.description} ${task.title}`;
        task_name =
            task && task.status != "finished"
                ? `T#${task_id} ${task.title}`
                : "无关联任务";
    }

    let query = ` SELECT * FROM eb_user_profile WHERE uid = ${uid} order by id desc `;

    const [rows] = await rds.query(
        `SELECT COUNT(*) as total FROM (${query}) AS subquery`
    );
    let { total } = rows[0] ?? { total: 0 };
    const totalPages = Math.ceil(total / limit);
    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;

    // 执行查询操作，获取结果rows
    query = `SELECT * FROM eb_user_profile WHERE uid = ${uid} and is_hide = 0 order by id desc LIMIT ${(page - 1) * limit}, ${page * limit}`;
    const profiles = await dbq.queryUserProfiles(
        query,
        [],
        req.session.crmROLE
    );

    const title = `管理用户档案`;
    const excerpt = `为U#${uid} ${user.real_name} 创建跟进记录 `;
    let description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()} 筛选条件： [U#${uid}] [T#${task_id}] `;
    description += pinfo == "" ? "[内容] " : `[内容：${uinfo}] `;

    const params = req.query;
    delete params.page;
    const link = `/apps/crm/users?${new URLSearchParams(params).toString()}`;
    const paginator = mui.generatePaginator(3, totalPages, page, link);

    res.render("crm/profiles", {
        totemUID,
        title,
        excerpt,
        description,
        user,
        profiles,
        task_id,
        task_name,
        paginator,
        begin,
        end,
        uid,
        pinfo,
        notification,
    });
});

router.get("/clients", async (req, res) => {
    let {
        rep = "",
        cid = "",
        cname = "",
        page = 1,
        limit = 50,
        begin = "2020-06-14",
        end = moment().format("YYYY-MM-DD"),
    } = req.query;

    if (
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
    )
        rep = req.session.authUserInfo.repCODE;
    //if ( req.session.authUserInfo.uid == 20450 || req.session.authUserInfo.uid == 20901 ) rep = 'PRB';
    const repCondition = ` and u.rep_code like '%${rep}%'`;

    const cidCondition = cid == "" ? "" : ` and u.uid = ${cid}`;
    const cnameCondition =
        cname == "" ? "" : ` and u.real_name like '%${cname}%'`;
    const timeCondition = ` and DATE_FORMAT(FROM_UNIXTIME(u.add_time), '%Y-%m-%d') >= '${begin}' AND DATE_FORMAT(FROM_UNIXTIME(u.add_time), '%Y-%m-%d') <= '${end}'`;

    // 计算时间点
    const endMoment = moment(req.query.end); // 截至日期
    const firstDayOfCurrentMonth = endMoment.clone().startOf("month"); // 当月第一天0点
    const lastDayOfCurrentMonth = endMoment.clone().endOf("month"); // 当月最后一天24点
    const firstDayOfLastMonth = firstDayOfCurrentMonth
        .clone()
        .subtract(1, "months"); // 上月第一天0点
    const firstDayOfThreeMonth = firstDayOfCurrentMonth
        .clone()
        .subtract(3, "months"); // 前三月第一天0点
    const lastDayOfLastMonth = lastDayOfCurrentMonth
        .clone()
        .subtract(1, "months")
        .endOf("month"); // 上月最后一天24点

    // 将时间点转换为UNIX时间戳
    const unixFirstDayCurrentMonth = firstDayOfCurrentMonth.unix();
    const unixLastDayCurrentMonth = lastDayOfCurrentMonth.unix();
    const unixFirstDayLastMonth = firstDayOfLastMonth.unix();
    const unixFirstDayThreeMonth = firstDayOfThreeMonth.unix();
    const unixLastDayLastMonth = lastDayOfLastMonth.unix();

    // 当月的开始时间（零点）和结束时间（24点）
    const startM0 = endMoment.clone().startOf("month").unix();
    const endM0 = endMoment.clone().endOf("month").unix();
    // 前一个月的开始时间（零点）和结束时间（24点）
    const startM1 = endMoment
        .clone()
        .subtract(1, "months")
        .startOf("month")
        .unix();
    const endM1 = endMoment.clone().subtract(1, "months").endOf("month").unix();
    // 前第二个月的开始时间（零点）和结束时间（24点）
    const startM2 = endMoment
        .clone()
        .subtract(2, "months")
        .startOf("month")
        .unix();
    const endM2 = endMoment.clone().subtract(2, "months").endOf("month").unix();
    // 前第三个月的开始时间（零点）和结束时间（24点）
    const startM3 = endMoment
        .clone()
        .subtract(3, "months")
        .startOf("month")
        .unix();
    const endM3 = endMoment.clone().subtract(3, "months").endOf("month").unix();
    // 最近半年以来
    const startHy = endMoment
        .clone()
        .subtract(6, "months")
        .startOf("month")
        .unix();
    const endHy = endM0;

    // SQL查询语句
    const query = `
        SELECT u.uid as cid, u.real_name as cname, u.agency, u.department, u.phone,
            -- 当月推荐数
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM0} AND r.noon_time < ${endM0} THEN r.recruited_count ELSE 0 END), 0) AS R0,
            -- 前第一个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM1} AND r.noon_time < ${endM1} THEN r.recruited_count ELSE 0 END), 0) AS R1,
            -- 前第二个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM2} AND r.noon_time < ${endM2} THEN r.recruited_count ELSE 0 END), 0) AS R2,
            -- 前第三个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM3} AND r.noon_time < ${endM3} THEN r.recruited_count ELSE 0 END), 0) AS R3,
            -- 当月首单数
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM0} AND r.noon_time < ${endM0} THEN r.first_order_count ELSE 0 END), 0) AS F0,
            -- 前第一个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM1} AND r.noon_time < ${endM1} THEN r.first_order_count ELSE 0 END), 0) AS F1,
            -- 前第二个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM2} AND r.noon_time < ${endM2} THEN r.first_order_count ELSE 0 END), 0) AS F2,
            -- 前第三个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM3} AND r.noon_time < ${endM3} THEN r.first_order_count ELSE 0 END), 0) AS F3,
            -- 过去六个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startHy} AND r.noon_time < ${endHy} THEN r.first_order_count ELSE 0 END), 0) AS Fh,
            -- 当月销售数
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM0} AND r.noon_time < ${endM0} THEN r.actual_weixin_paid ELSE 0 END), 0) AS S0,
            -- 前第一个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM1} AND r.noon_time < ${endM1} THEN r.actual_weixin_paid ELSE 0 END), 0) AS S1,
            -- 前第二个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM2} AND r.noon_time < ${endM2} THEN r.actual_weixin_paid ELSE 0 END), 0) AS S2,
            -- 前第三个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startM3} AND r.noon_time < ${endM3} THEN r.actual_weixin_paid ELSE 0 END), 0) AS S3,
            -- 过去六个月
            COALESCE(SUM(CASE WHEN r.noon_time >= ${startHy} AND r.noon_time < ${endHy} THEN r.actual_weixin_paid ELSE 0 END), 0) AS Sh,
            COALESCE(SUM(r.recruited_count), 0) AS Ra,
            COALESCE(SUM(r.first_order_count), 0) AS Fa,
            COALESCE(SUM(r.qpo_count), 0) AS Qa,
            COALESCE(SUM(r.actual_weixin_paid), 0) AS Sa,
            u.rep_code as rep, u.now_money as tupay,
            u.rep_id as rid
        FROM tutem_mall.eb_user u
        LEFT JOIN tutem_mall.eb_spreader_daily_report r ON u.uid = r.spreader_uid
        WHERE ( u.promoter_type = 1 or u.promoter_type = 2 or u.promoter_type = 5 or u.promoter_type = 7 )  ${repCondition} ${cidCondition} ${cnameCondition} ${timeCondition}
        GROUP BY u.uid
        ORDER BY Fh DESC, Sh DESC, S1 DESC, F1 DESC, R1 DESC, S2 DESC, F2 DESC, R2 DESC, S3 DESC, F3 DESC, R3 DESC, Fa DESC, Ra DESC, Sa DESC, cid ASC
    `;

    const connection = await rds.pool.getConnection();

    const [totalRows] = await connection.execute(
        `SELECT COUNT(*) as total, sum(Ra) as sumR, sum(Fa) as sumF, sum(Qa) as sumQ, FLOOR(sum(Sa)) as sumAmount FROM (${query}) AS subquery`
    );
    let { total, sumR, sumF, sumQ, sumAmount } = totalRows[0] ?? {
        total: 0,
        sumR: 0,
        sumF: 0,
        sumQ: 0,
        sumAmount: 0,
    };
    if (total == 0) {
        sumR = sumF = sumQ = 0;
    } //当没有记录时total = 0, 其它为null
    const totalPages = Math.ceil(total / limit);
    const startIndex = (page - 1) * limit;
    const endIndex = startIndex + limit;

    // 执行查询操作，获取结果rows
    const [rows] = await connection.execute(
        query + ` LIMIT ${startIndex}, ${endIndex}`
    );
    //console.log('clients query:', query + ` LIMIt ${startIndex}, ${endIndex}`);
    connection.release(); //如果使用pool就应该relase

    const modifiedRows = rows.map((row) => {
        let {
            rid,
            rep,
            cname,
            phone,
            agency,
            department,
            tupay,
            R0,
            R1,
            R2,
            R3,
            F0,
            F1,
            F2,
            F3,
            Fh,
            S0,
            S1,
            S2,
            S3,
            Sh,
            Ra,
            Fa,
            Qa,
            Sa,
        } = row;

        tupay = parseInt(tupay).toString();
        S0 = parseInt(S0).toString();
        S1 = parseInt(S1).toString();
        S2 = parseInt(S2).toString();
        S3 = parseInt(S3).toString();
        Sa = parseInt(Sa).toString();
        Sh = parseInt(Sh).toString();

        if (req.session.crmROLE !== "crm-admin")
            agency = tutils.maskString(agency);
        //用户如果不是管理用户，也不是客户代表，则不显示图贝，并加强客户隐私保护
        if (
            rid != req.session.repID &&
            req.session.crmROLE !== "crm-admin" &&
            req.session.crmROLE !== "crm-gmoffc" &&
            req.session.crmROLE !== "crm-mkting"
        ) {
            cname = tutils.maskString(cname);
            phone = tutils.maskString(phone);
            agency = tutils.maskString(agency);
            department = tutils.maskString(department);
            tupay = "**";
        }
        cname = `${cname} ${phone} ${agency} ${department}`;

        const modifiedRow = { ...row, cname, tupay, S0, S1, S2, S3, Sa, Sh };
        const keysToDelete = ["phone", "agency", "department", "rid"];
        keysToDelete.forEach((key) => {
            delete modifiedRow[key];
        });
        //if ( role !== 'crm-admin' && role != 'crm-gmoffc' ) { delete modifiedRow.amount; }

        return modifiedRow;
    });

    const fields = [
        "序号",
        "姓名",
        "R0",
        "R1",
        "R2",
        "R3",
        "F0",
        "F1",
        "F2",
        "F3",
        "Fh",
        "S0",
        "S1",
        "S2",
        "S3",
        "Sh",
        "Ra",
        "Fa",
        "Qa",
        "Sa",
        "REP",
        "TUBAY",
    ];
    const params = req.query;
    delete params.page; //删除req.query中的page参数以便后面增加
    const link = `/apps/crm/clients?${new URLSearchParams(params).toString()}`;
    const paginator = mui.generatePaginator(3, totalPages, page, link);

    const title = `客户列表`;
    const excerpt = `推荐${sumR} 首单${sumF} 合格${sumQ} 销量${sumAmount}`;
    const tips = `<span title='[D] 统计截日 [R] 新荐 [F] 首患 [Q] 合格 [S] 实付尚未扣退款 [X0] D日当月内 [X1] D日前第一个月内 [X2] D日前第二个月内 [X3] D日前底三个月内 [Xa] 全部汇总' style='color:#88b048'>数据说明</span>`;
    const description = `[${begin},${end}] 共${total}记录 P${page}/${totalPages}<br>${req.session.crmROLE.toUpperCase()} 筛选条件： [代表${rep}] [C#${cid}] [姓名${cname}] ${tips}`;

    const download =
        req.session.crmROLE == "crm-admin" ||
        req.session.crmROLE == "crm-gmoffc"
            ? `
        <span><a href="https://totemlife.cn/apps/download-excel?sql=${encodeURIComponent(query)}&filename=clients-report.xls"><span class="fas fa-download"></i></a></span>
    `
            : "";

    res.render("crm/clients", {
        title: title,
        excerpt: excerpt,
        description: description,
        fields: fields,
        rows: modifiedRows,
        paginator: paginator,
        begin,
        end,
        cid,
        cname,
        rep,
        download,
    });
});

router.get("/materials", async (req, res) => {
    const { uid } = req.session.user || 1414;
    const cid = req.query.cid || uid;
    const tag = req.query.tag || "推广物料";
    const connection = await rds.pool.getConnection();
    const query = `SELECT * FROM tutem_mall.eb_mta_materials where tags like '%${tag}%'`;
    const [rows] = await connection.execute(query);
    connection.release();
    res.render("crm/materials", {
        title: `C#${cid}的${tag}`,
        materials: rows,
        cid: cid,
    });
});

// Function to fetch the last 20 comments from the database
async function fetchComments() {
    const connection = await rds.pool.getConnection();
    const [rows] = await connection.execute(
        "SELECT * FROM tutem_mall.eb_daily_standup WHERE comment IS NOT NULL AND comment != '' ORDER BY id DESC LIMIT 20"
    );
    connection.release();

    let comments = rows.map((row) => {
        const date = moment.unix(row.add_time).format("YYYY-MM-DD");
        const emoji =
            row.light === 1
                ? "☹️"
                : row.light === 2
                  ? "😐"
                  : row.light === 3
                    ? "😀"
                    : "";
        return {
            id: row.id,
            message: `${emoji} ${row.comment} — 匿名同事 ${date}`,
        };
    });

    // Fisher-Yates shuffle algorithm
    for (let i = comments.length - 1; i > 0; i--) {
        const j = Math.floor(Math.random() * (i + 1));
        [comments[i], comments[j]] = [comments[j], comments[i]];
    }

    return comments;
}

// Function to select a random quote from the array
function fetchRandomQuote() {
    const quotes = [
        "真实的我与他人的关系，是我们的内在成长的基础 — 卡尔·罗杰斯",
        "人生的价值在于我们所追求的目标，这些目标决定我们是谁。 — 罗洛·梅",
        "在追求（目标）过程中，我们与他人建立的关系，使我们的生活充满了意义和价值。 — 罗洛·梅",
        "我们是自己行为的产物。 — 让-保罗·萨特",
        "一个人并非以出生的方式而存在，而是以他自己的行动而存在。 — 西蒙·波娃",
        "一个人必须在他人的眼中找到自己的存在。 — 西蒙·波娃",
        "所有的实际生活都是遇见。 — 马丁·布伯",
        "人们只有在面对困难和挑战时，才能真正发现自己的潜力和价值。 — 弗里德里希·尼采",
        "人不是一个孤立的存在，而是通过与他人的关系和他对世界的理解来定义自己。 — 索伦·奇尔克郭尔",
        "人不是一个封闭的自我，而是一个开放的存在，我们的存在是在世界中的存在。 — 马丁·海德格尔",
        "人与人的真实关系是成长的土壤和源泉。 — 卡尔·罗杰斯",
        "每一个人都是他人的镜子，我们可以通过他人来看到我们自己的反映。 — 欧文·达洛",
        "人是他所爱的，更是他为之战斗的。 — 阿尔贝·加缪",
        "我们的存在是以与他人的关系为基础的。 — 路德维希·宾斯瓦格",
        "通过与他人的关系，我们才能发现自己的存在和价值。 — 梅达德·博斯",
        "我们的价值不在于我们是谁，而在于我们可以成为谁。 — 弗里德里希·尼采",
        "不是出生在女性身体中使我成为女性，而是社会如何看待我，如何期待我去行动。 — 西蒙·波娃",
        "人与人的相遇，如同两种化学物质的相遇，如果有任何反应，都会各自改变。 — 卡尔·荣格",
    ];

    const randomQuoteIndex = Math.floor(Math.random() * quotes.length);
    return quotes[randomQuoteIndex];
}

router.get("/get-target-tip", async (req, res) => {
    // 获取请求参数
    const { type, id } = req.query;

    // 定义数据获取规则
    const rules = {
        user: {
            sql: `
            SELECT
                CONCAT(
                    'U#', u.uid, ' ', SUBSTRING(u.real_name, 1, 2), '**<br>',
                    'C#', u.spread_uid, ' ', DATE_FORMAT(FROM_UNIXTIME(u.add_time), '%Y-%m-%d'), ' ', u.login_type, '<br>',
                    CASE WHEN COALESCE(u.goods_list, '') = '' THEN '' ELSE CONCAT('最后购买：',u.goods_list, '<br>') END,
                    CASE WHEN COALESCE(u.purchased, '') = '' THEN '无购买历史信息' ELSE CONCAT('购买历史：',u.purchased)  END
                ) AS tips
            FROM tutem_mall.eb_user u
            WHERE u.uid = ?;
            `,
            more: "/apps/crm/clients?type=all&cid=",
        },
        order: {
            sql: `
            SELECT
                CONCAT(
                    'U#', uid, SUBSTRING(real_name, 1, 1), '** ***', SUBSTRING(user_phone, -3), '<br>',
                    DATE_FORMAT(FROM_UNIXTIME(pay_time), '%Y-%m-%d'), '<br>',
                    tags
                ) AS tips
            FROM
                tutem_mall.eb_store_order
            WHERE
                id = ?
            `,
            more: "#",
        },
        client: {
            sql: `
            SELECT
                CONCAT(
                    'C#', u.uid, ' ', SUBSTRING(u.real_name, 1, 1), '*',
                    ' P', u.is_promoter, 'T', u.promoter_type, '<br>',
                    SUBSTRING(u.agency, 1, 3), '** ', u.rep_code
                ) AS tips
            FROM tutem_mall.eb_user u
            WHERE u.uid = ?;
            `,
            more: "/apps/crm/clients?type=all&cid=",
        },
    };

    // 根据type获取相应的规则
    const rule = rules[type];

    if (!rule) {
        res.status(400).send("Invalid type parameter");
        return;
    }

    // 执行SQL命令，获取数据
    const connection = await rds.pool.getConnection();
    const [rows] = await connection.execute(rule.sql, [id]);
    connection.release();

    if (rows.length === 0) {
        res.send(
            `无此纪录<br><a href='${rule.more}${id}><h5>查看详情</h5></a>`
        ); // 如果查询结果为空，则返回空字符串
    } else {
        // 构造返回的字符串
        const tips = rows[0].tips;
        const result = `${tips}<br><a href='${rule.more}${id}'><h5>查看详情</h5></a>`;
        res.send(result);
    }
});

router.get("/show-audit-log", async (req, res) => {
    const { action = "CHANGE_PROMOTER_TYPE", rep = "" } = req.query;

    let sql = `select admin_id as admin, from_unixtime(a.add_time,'%Y-%m-%d %H:%i:%s') as time, concat('U#',a.target_id, ' ', u.real_name, ' T',u.promoter_type) as target_user, u.rep_code as tinfo, a.memo from tutem_mall.eb_audit a left join tutem_mall.eb_user u on (a.target_id = u.uid) where 1=1 `;
    if (action != "") sql += ` and a.action like '%${action}%'`;
    if (rep != "") sql += ` and u.rep_code like '%${rep}%'`;
    sql += ` order by a.add_time desc;`;

    const downloadLink =
        req.session.crmROLE == "crm-admin" ||
        req.session.crmROLE == "crm-gmoffc"
            ? `
        <span><a href="https://totemlife.cn/apps/download-excel?sql=${encodeURIComponent(sql)}&filename=${action}-report.xls"><span class="fas fa-download"></i></a></span>
    `
            : "";

    const [rows] = await rds.query(sql);
    const fields = ["A#", "时间", "对象", "说明", "内容"];
    res.render("results", { fields, rows, downloadLink });
});

router.get("/", async (req, res) => {
    const repParam =
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
            ? `&rep=${req.session.authUserInfo.repCODE}`
            : "";
    const userParam =
        req.session.crmROLE === "crm-sales" ||
        req.session.crmROLE === "crm-channel"
            ? `&userId=${req.session.authUserInfo.uid}`
            : "";
    const group1 = [
        {
            href: "/apps/crm/promotions?team=SALES",
            title: "推广绩效",
            description: "统计口径默认为周",
            icon: "fas fa-bullhorn",
        },
        {
            href: "/apps/crm/referrals?team=SALES",
            title: "推荐统计",
            description: "统计口径默认为周",
            icon: "fas fa-share-alt",
        },
        {
            href: "/apps/doctor-performance/chart",
            title: "趋势分析",
            description: "客户业绩趋势图表分析",
            icon: "fas fa-chart-line",
        },
        {
            href: `/apps/crm/sales-leads?${userParam}`,
            title: "销售线索",
            description: "线索管理有分级权限",
            icon: "fas fa-broadcast-tower",
        },
    ];
    const group2 = [
        {
            href: `/apps/crm/clients?${repParam}`,
            title: "客户管理",
            description: "客户管理有分级权限",
            icon: "fas fa-address-book",
        },
        {
            href: `/apps/crm/users?${repParam}`,
            title: "用户管理",
            description: "用户管理有分级权限",
            icon: "fas fa-users",
        },
        {
            href: `/apps/crm/orders?${repParam}`,
            title: "订单管理",
            description: "订单管理有分级权限",
            icon: "fas fa-file-invoice",
        },
    ];
    const group3 = [
        {
            href: `/apps/crm/tasks?${repParam}`,
            title: "任务管理",
            description: "任务管理有分级权限",
            icon: "fas fa-list-alt",
        },
        {
            href: "/apps/crm/materials?tag=产品资料",
            title: "产品资料",
            description: "最全入口持续更新",
            icon: "fas fa-database",
        },
        {
            href: "/apps/tools/poster",
            title: "推广海报",
            description: "生成推广海报",
            icon: "fas fa-database",
        },
        {
            href: "/apps/admin",
            title: "其它管理",
            description: "其它管理功能入口",
            icon: "fas fa-comment-dots",
        },
    ];
    const comments = await fetchComments();
    const quote = fetchRandomQuote();
    res.render("crm/home", {
        group1,
        group2,
        group3,
        quote,
        comments,
        authUserInfo: req.session.authUserInfo,
    });
});

// 销售规划任务列表页
router.get("/sales-plans", async (req, res) => {
    // 只允许管理员访问
    if (req.session.crmROLE !== "crm-admin") {
        return res.render("warn", {
            title: "权限提示",
            message: "您没有访问此页面的权限",
            description: "请联系管理员",
        });
    }

    const connection = await rds.pool.getConnection();
    const [plans] = await connection.execute(`
        SELECT p.*, u.real_name as creator_name
        FROM sales_plans p
        LEFT JOIN eb_user u ON p.creator_uid = u.uid
        ORDER BY p.created_at DESC
    `);

    // 添加状态判断属性
    const modifiedPlans = plans.map((plan) => ({
        ...plan,
        draft: plan.status === "draft",
        published: plan.status === "published",
        closed: plan.status === "closed",
        review_start_date: moment(plan.review_start_date).format(
            "YYYY-MM-DD HH:mm:ss"
        ),
        review_end_date: moment(plan.review_end_date).format(
            "YYYY-MM-DD HH:mm:ss"
        ),
        plan_start_date: moment(plan.plan_start_date).format(
            "YYYY-MM-DD HH:mm:ss"
        ),
        plan_end_date: moment(plan.plan_end_date).format("YYYY-MM-DD HH:mm:ss"),
        created_at: moment(plan.created_at).format("YYYY-MM-DD HH:mm:ss"),
    }));

    connection.release();

    res.render("crm/sales-plans", {
        title: "销售规划任务管理",
        excerpt: `共${plans.length}个规划任务`,
        description: "管理销售规划任务",
        plans: modifiedPlans,
    });
});

// 创建规划任务页面
router.get("/create-sales-plan", async (req, res) => {
    if (req.session.crmROLE !== "crm-admin") {
        return res.render("warn", {
            title: "权限提示",
            message: "您没有访问此页面的权限",
            description: "请联系管理员",
        });
    }

    res.render("crm/create-sales-plan", {
        title: "创建销售规划任务",
        totemUID: req.session.totemUID,
    });
});

// 创建规划任务API
router.post("/api/sales-plans", async (req, res) => {
    if (req.session.crmROLE !== "crm-admin") {
        return res.status(403).json({ error: "没有权限" });
    }

    const {
        title,
        description,
        review_start_date,
        review_end_date,
        plan_start_date,
        plan_end_date,
    } = req.body;

    // 处理日期，将结束日期设置为当天的23:59:59
    const processedReviewEndDate = moment(review_end_date)
        .endOf("day")
        .format("YYYY-MM-DD HH:mm:ss");
    const processedPlanEndDate = moment(plan_end_date)
        .endOf("day")
        .format("YYYY-MM-DD HH:mm:ss");
    // 开始日期设置为当天的00:00:00
    const processedReviewStartDate = moment(review_start_date)
        .startOf("day")
        .format("YYYY-MM-DD HH:mm:ss");
    const processedPlanStartDate = moment(plan_start_date)
        .startOf("day")
        .format("YYYY-MM-DD HH:mm:ss");

    const connection = await rds.pool.getConnection();
    try {
        await connection.execute(
            `
            INSERT INTO sales_plans
            (title, description, status, review_start_date, review_end_date,
             plan_start_date, plan_end_date, creator_uid)
            VALUES (?, ?, 'draft', ?, ?, ?, ?, ?)
        `,
            [
                title,
                description,
                processedReviewStartDate,
                processedReviewEndDate,
                processedPlanStartDate,
                processedPlanEndDate,
                req.session.totemUID,
            ]
        );

        res.json({ success: true });
    } catch (err) {
        console.error("Create sales plan error:", err);
        res.status(500).json({ error: "创建失败" });
    } finally {
        connection.release();
    }
});

// 更新规划任务状态API
router.put("/api/sales-plans/:id/status", async (req, res) => {
    if (req.session.crmROLE !== "crm-admin") {
        return res.status(403).json({ error: "没有权限" });
    }

    const { id } = req.params;
    const { status } = req.body;

    const connection = await rds.pool.getConnection();
    try {
        await connection.execute(
            "UPDATE sales_plans SET status = ? WHERE id = ?",
            [status, id]
        );
        res.json({ success: true });
    } catch (err) {
        console.error("Update sales plan status error:", err);
        res.status(500).json({ error: "更新失败" });
    } finally {
        connection.release();
    }
});

// 添加删除API
router.delete("/api/sales-plans/:id", async (req, res) => {
    if (req.session.crmROLE !== "crm-admin") {
        return res.status(403).json({ error: "没有权限" });
    }

    const { id } = req.params;
    const connection = await rds.pool.getConnection();

    try {
        // 先检查状态是否为draft
        const [rows] = await connection.execute(
            "SELECT status FROM sales_plans WHERE id = ?",
            [id]
        );

        if (rows.length === 0) {
            throw new Error("任务不存在");
        }

        if (rows[0].status !== "draft") {
            throw new Error("只能删除未发布的任务");
        }

        // 执行删除
        await connection.execute(
            'DELETE FROM sales_plans WHERE id = ? AND status = "draft"',
            [id]
        );

        res.json({ success: true });
    } catch (err) {
        console.error("Delete sales plan error:", err);
        res.status(500).json({ error: err.message || "删除失败" });
    } finally {
        connection.release();
    }
});

// 区域复盘首页
router.get("/regional-reviews", async (req, res) => {
    // 获取查询参数，默认为当前月份
    const { month = moment().format('YYYY-MM') } = req.query;
    const startDate = moment(month).startOf('month').format('YYYY-MM-DD');
    const endDate = moment(month).endOf('month').format('YYYY-MM-DD');
    const today = moment().format('YYYY-MM-DD');
    const yesterday = moment().subtract(1, 'days').format('YYYY-MM-DD');

    // 查询当月所有复盘数据
    const query = `
        SELECT r.*, u.real_name, u.rep_code
        FROM regional_reviews r
        JOIN eb_user u ON r.user_id = u.uid
        WHERE r.review_date BETWEEN ? AND ?
        ORDER BY r.review_date DESC, r.user_id
    `;

    const [reviews] = await rds.query(query, [startDate, endDate]);

    // 按用户分组汇总数据
    const userSummaries = {};
    reviews.forEach(review => {
        const userId = review.user_id;
        if (!userSummaries[userId]) {
            userSummaries[userId] = {
                userId,
                userName: review.real_name,
                repCode: review.rep_code,
                reviewCount: 0,
                newCustomerContacts: 0,
                existingCustomerContacts: 0,
                onlineOrders: 0,
                offlineSales: 0,
                mediaContents: 0,
                mediaFollowers: 0
            };
        }

        const content = JSON.parse(review.content);
        userSummaries[userId].reviewCount++;

        // 累加各项数据
        if (content.newCustomers) {
            userSummaries[userId].newCustomerContacts += parseInt(content.newCustomers.contactCount || 0);
        }

        if (content.existingCustomers && Array.isArray(content.existingCustomers)) {
            userSummaries[userId].existingCustomerContacts += content.existingCustomers.length;
        }

        if (content.sales) {
            userSummaries[userId].onlineOrders += parseInt(content.sales.onlineOrderCount || 0);
            userSummaries[userId].offlineSales += parseFloat(content.sales.offlineAmount || 0);
        }

        if (content.media) {
            userSummaries[userId].mediaContents += parseInt(content.media.contentCount || 0);
            userSummaries[userId].mediaFollowers += parseInt(content.media.followerCount || 0);
        }
    });

    // 检查当前用户是否已提交昨日复盘
    const userId = req.session.totemUID;
    const [existingReview] = await rds.query(
        'SELECT id FROM regional_reviews WHERE user_id = ? AND review_date = ?',
        [userId, yesterday]
    );

    const hasSubmittedYesterday = existingReview.length > 0;

    // 获取所有客户列表（用于选择客户）
    const [clients] = await rds.query(`
        SELECT uid, real_name, phone
        FROM eb_user
        WHERE is_promoter = 1
        ORDER BY uid DESC
        LIMIT 1000
    `);

    res.render("crm/regional-reviews", {
        title: "区域复盘",
        excerpt: `${month}月复盘数据汇总`,
        description: `从${startDate}到${endDate}的复盘数据`,
        month,
        today,
        yesterday,
        summaries: Object.values(userSummaries),
        hasSubmittedYesterday,
        clients,
        userId
    });
});

// 提交复盘数据
router.post("/api/regional-reviews", async (req, res) => {
    try {
        const userId = req.session.totemUID;
        const { reviewDate, content } = req.body;

        // 验证数据
        if (!reviewDate || !content) {
            return res.status(400).json({ success: false, message: "缺少必要参数" });
        }

        // 检查是否已存在该日期的复盘
        const [existingReview] = await rds.query(
            'SELECT id FROM regional_reviews WHERE user_id = ? AND review_date = ?',
            [userId, reviewDate]
        );

        if (existingReview.length > 0) {
            // 更新现有记录
            await rds.query(
                'UPDATE regional_reviews SET content = ? WHERE id = ?',
                [JSON.stringify(content), existingReview[0].id]
            );
        } else {
            // 创建新记录
            await rds.query(
                'INSERT INTO regional_reviews (user_id, review_date, content) VALUES (?, ?, ?)',
                [userId, reviewDate, JSON.stringify(content)]
            );
        }

        res.json({ success: true, message: "复盘数据已保存" });
    } catch (error) {
        console.error("保存复盘数据错误:", error);
        res.status(500).json({ success: false, message: "服务器错误" });
    }
});

// 获取特定日期的复盘数据
router.get("/api/regional-reviews/:date", async (req, res) => {
    try {
        const userId = req.session.totemUID;
        const { date } = req.params;

        const [review] = await rds.query(
            'SELECT content FROM regional_reviews WHERE user_id = ? AND review_date = ?',
            [userId, date]
        );

        if (review.length > 0) {
            res.json({ success: true, data: JSON.parse(review[0].content) });
        } else {
            res.json({ success: false, message: "未找到数据" });
        }
    } catch (error) {
        console.error("获取复盘数据错误:", error);
        res.status(500).json({ success: false, message: "服务器错误" });
    }
});

module.exports = router;
